﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using DAL.Entities;

namespace DAL.EntitiesDAL
{
    public class chitietphieuDAL: BaseDAL,UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @" SELECT * FROM chitietphieu";
            return ExecuteQuery(sql);
        }

        public string Select_tenmon(string mon, string mp)
        {
            var sql = @"SELECT soluong
                            FROM chitietphieu
                            WHERE  mamon= '{0}' and maphieu = '{1}'";
            sql = string.Format(sql, mon, mp);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["soluong"] + " " : "0";
        }

        public bool doi_soluong(string sl, string mp, string mon)
        {
            string sql = @"SET dateformat dmy;UPDATE chitietphieu SET soluong= '{0}' WHERE MAPHIEU='{1}' and mamon = '{2}'";
            sql = string.Format(sql, sl, mp, mon);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable load_monhuy(string ten)
        {
            var sql = @" 
                            select a.mamon as id, tenmon as ten
                            from chitietphieu a, thucdon b
                            where a.mamon = b.mamon
                            and a.maphieu = '{0}'
                            ";
            sql = string.Format(sql, ten);
            return ExecuteQuery(sql);
        }

        public DataTable loadban()
        {
            string sql = @" SELECT * FROM BAN";
            return ExecuteQuery(sql);
        }

        public DataTable load_khuvuc()
        {
            string sql = @" SELECT makv as id, tenkv as ten FROM khuvuc";
            return ExecuteQuery(sql);
        }

        public DataTable load()
        {
            string sql = @" SELECT * FROM PHIEU";
            return ExecuteQuery(sql);
        }

        public DataTable load_ban()
        {
            string sql = @" 
                            select a.maban as id, a.tenban as ten from ban a where a.maban in (select b.maban from phieu b)
                            ";
            return ExecuteQuery(sql);
        }

        public DataTable mon()
        {
            var sql = @" SELECT mamon AS stt, tenmon AS ten FROM thucdon";
            return ExecuteQuery(sql);
        }

        public string Select_dongia(string ten)
        {
            var sql = @"SELECT dongia
                            FROM thucdon
                            WHERE  mamon= '{0}'";
            sql = string.Format(sql, ten);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["dongia"] + " " : "0";
        }

        public string Select_maphieu(string ten)
        {
            var sql = @"SELECT maphieu
                            FROM phieu
                            WHERE  maban= '{0}'";
            sql = string.Format(sql, ten);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["maphieu"] + " " : "0";
        }

        public DataTable tenNV()
        {
            var sql = @" SELECT manv AS stt, hoten AS ten FROM nhanvien";
            return ExecuteQuery(sql);
        }
        public DataTable maphieu()
        {
            var sql = @" SELECT MAPHIEU AS MAPHIEU FROM phieu";
            return ExecuteQuery(sql);
        }
        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            throw new NotImplementedException();
        }

        public bool xoa(string id, string pass)
        {
            string sql = @"DELETE FROM CHITIETPHIEU WHERE MAPHIEU='{0}' and MAMON=N'{1}'";
            sql = string.Format(sql, id, pass);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (chitietphieu)obj;
            string sql = @"INSERT INTO CHITIETPHIEU(MAPHIEU,MAMON,GIAMGIA,SOLUONG,DONGIA, MANV,TINHTIEN) VALUES('{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}','{6}') ";
            sql = string.Format(sql, o.MAPHIEU, o.MAMON, o.GIAMGIA, o.SOLUONG, o.DONGIA, o.MANV, o.TINHTIEN);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (chitietphieu)obj;
            string sql = @"SET dateformat dmy;UPDATE CHITIETPHIEU SET GIAMGIA=N'{2}',SOLUONG=N'{3}',DONGIA=N'{4}',MANV=N'{5}', TINHTIEN=N'{6}' WHERE MAPHIEU='{0}' and MAMON=N'{1}'";
            sql = string.Format(sql, o.MAPHIEU, o.MAMON, o.GIAMGIA, o.SOLUONG, o.DONGIA, o.MANV, o.TINHTIEN);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable loc_tenban(string ten)
        {
           
                var sql = @"
                            select *
                            from chitietphieu a, phieu b
                            where a.maphieu = b.maphieu
                            
                            and b.maban = '{0}'
                            ";
                sql = string.Format(sql, ten);
                return ExecuteQuery(sql);
            
        }

        public DataTable check_chitietphieu(string stt, string matp)
        {

            var sql = @"
                            select maphieu, mamon
                            from chitietphieu
                            where
                            maphieu= '{0}'
                            and mamon = '{1}'
                             
                            ";
            sql = string.Format(sql, stt, matp);
            return ExecuteQuery(sql);

        }

        public string ktra(string stt, string mamon)
        {

            var sql = @"
                            select soluong
                            from chitietphieu
                            where
                            maphieu= '{0}'
                            and mamon = '{1}'
                             
                            ";
            sql = string.Format(sql, stt, mamon);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["soluong"] + " " : "0";
        }

        public string soluong(string stt)
        {

            var sql = @"
                            select soluong
                            from chitietphieu
                            where
                            maphieu= '{0}'
                            
                             
                            ";
            sql = string.Format(sql, stt);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["soluong"] + " " : "null";
        }
    }
}
